﻿Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports Microsoft.Practices.EnterpriseLibrary.Data.Oracle
Imports System.Data.OracleClient
Imports System.Data
Imports System.Data.EntityClient
Imports System.Text
Imports System.IO
Imports System.Xml
Imports System.Xml.XPath

Public Class Controller

    Public Function GetData(ByVal db As Database, ByVal tableInfo As TableInfo) As Object

        ' SQL文作成
        Dim selectSql As StringBuilder = New StringBuilder()
        Dim result As Object = Nothing
        Dim sKey() As String
        Dim svalue() As String
        sKey = tableInfo.keyName.Split(",")
        svalue = tableInfo.keyValue.Split(",")
        If sKey.Length <> svalue.Length Then
            MsgBox("検索条件のフィールド名と値はペアに設定しなければならないです。")
            Return Nothing
        End If
        selectSql.AppendFormat("SELECT " + tableInfo.binaryKey + " FROM " + tableInfo.tableName)
        selectSql.AppendLine(" WHERE 1=1 ")
        For i = 0 To sKey.Count - 1
            selectSql.AppendLine(" AND " + sKey(i) + " = '" + svalue(i) + "'")
        Next

        ' データを取得
        Try
            Using cmd As System.Data.Common.DbCommand = db.GetSqlStringCommand(selectSql.ToString())
                result = db.ExecuteScalar(cmd)
            End Using
            If (result Is Nothing) Then
                MsgBox("データが見つかれません。")
                Return Nothing
            End If
        Catch ex As Exception
            MsgBox("データベースエラー。Exception:" + ex.Message)
            Return Nothing
        End Try

        Return result
    End Function

    Public Function UpdateData(ByVal db As Database, ByVal tableInfo As TableInfo, _
                               ByVal data As Object, ByRef DataType As Type, _
                               ByVal encoding As String) As Boolean
        ' SQL文作成
        Dim sql As StringBuilder = New StringBuilder()
        Dim result As Boolean = False
        Dim sKey() As String
        Dim svalue() As String
        sKey = tableInfo.keyName.Split(",")
        svalue = tableInfo.keyValue.Split(",")
        If sKey.Length <> svalue.Length Then
            MsgBox("検索条件のフィールド名と値はペアに設定しなければならないです。")
        End If
        sql.AppendFormat("Update " + tableInfo.tableName)
        sql.AppendLine(" SET " + tableInfo.binaryKey)
        sql.AppendLine(" = " + Controller.BuildParameterName(db, "UPDPLACE"))
        sql.AppendLine(" WHERE 1=1 ")
        For i = 0 To sKey.Count - 1
            sql.AppendLine(" AND " + sKey(i) + " = '" + svalue(i) + "'")
        Next

        Dim btContent() As Byte
        Dim utf8Encoding As New System.Text.UTF8Encoding
        Dim shiftJisEncoding As System.Text.Encoding

        '更新内容


        ' データを取得
        Try
            Using cmd As System.Data.Common.DbCommand = db.GetSqlStringCommand(sql.ToString())
                Select Case DataType.Name
                    Case "String"
                        db.AddParameter(cmd, "UPDPLACE",
                                                DbType.String,
                                                data.Length,
                                                ParameterDirection.Input,
                                                False, 0, 0, "",
                                                DataRowVersion.Default,
                                                data)
                    Case "Byte[]"
                        If encoding = "UTF-8" Then
                            btContent = utf8Encoding.GetBytes(data)
                        Else
                            shiftJisEncoding = System.Text.Encoding.GetEncoding(encoding)
                            btContent = shiftJisEncoding.GetBytes(data)
                        End If

                        db.AddInParameter(cmd, "UPDPLACE", DbType.Binary, btContent)

                    Case "Int32"
                        db.AddInParameter(cmd, "UPDPLACE", DbType.Int32, data)

                End Select
                result = db.ExecuteNonQuery(cmd)
            End Using
            If Not result Then
                MsgBox("更新更新処理に失敗しました、データが見つかれません。")
            Else
                MsgBox("内容を正常に更新しました。")
            End If
        Catch ex As Exception
            MsgBox("データベースエラー。Exception:" + ex.Message)
            result = False
        End Try

        Return result
    End Function

    Public Function ModifyXML(ByRef data As Object, ByVal PathString As String, ByVal PathValue As String) As Boolean
        Try
            Dim doc As XmlDocument = New XmlDocument()
            Dim xmlString As String = String.Empty
            Select Case data.GetType().Name
                Case "Byte[]"
                    xmlString = System.Text.Encoding.UTF8.GetString(data)
                Case Else
                    xmlString = data.ToString
            End Select

            doc.LoadXml(xmlString)
            Dim nodeCount As Integer, tmpCount As Integer = 0
            Dim path As String(), value As String()
            path = PathString.Split(vbCrLf)
            value = PathValue.Split(vbCrLf)
            For Each s As String In path
                s = s.Trim()
            Next
            For Each s As String In value
                s = s.Trim()
            Next
            nodeCount = path.Length

            For i As Integer = 1 To path.Length
                Dim node As XmlNode = doc.SelectSingleNode(path(i - 1))

                If (Not node Is Nothing) Then
                    If "[null]".Equals(value(i - 1).ToLower().Trim()) Then
                        value(i - 1) = String.Empty
                    End If
                    node.InnerText = value(i - 1).Trim()
                Else
                    MsgBox("[" & i & "]番のパスでXMLにノードを選択されない(パース不正の可能性がある)。")
                    tmpCount += 1
                End If
            Next
            If tmpCount > 0 Then
                MsgBox(tmpCount & "ノード更新されない。もう一度確認ください。")
                If tmpCount = nodeCount Then
                    Return False
                End If
            End If

            data = CType(doc.InnerXml.ToString, Object)

        Catch ex As Exception
            MsgBox("XML編集失敗。Exception:" + ex.Message)
            Return False
        End Try

        Return True
    End Function

    Function WriteFile(ByVal sPath As String, ByVal obj As Object) As Boolean
        Try
            Dim fileStream As New System.IO.FileStream(sPath, FileMode.Create)
            Dim binaryWriter As New System.IO.BinaryWriter(fileStream, System.Text.Encoding.UTF8)
            Select Case obj.GetType().Name
                Case "Byte[]"
                    binaryWriter.Write(obj)
                Case Else
                    binaryWriter.Write(System.Text.Encoding.UTF8.GetBytes(obj))
            End Select

            binaryWriter.Close()
            fileStream.Close()
            MsgBox("ファイル保存終了。")
        Catch ex As Exception
            MsgBox("ファイル保存失敗。Exception:" + ex.Message)
            Return False
        End Try
        Return True
    End Function

#Region "DBプロバイダによって異なるバインドパラメータ名を生成して返す"
    ''' <summary>
    ''' DBプロバイダによって異なるバインドパラメータ名を生成して返す。
    ''' </summary>
    ''' <param name="db"></param>
    ''' <param name="name"></param>
    Public Shared Function BuildParameterName(ByRef db As Database, ByVal name As String) As String
        If TypeOf db.DbProviderFactory Is System.Data.SqlClient.SqlClientFactory Then
            ' SQL Server
            'Return db.BuildParameterName(name)
            Return "@" & name
        Else
            ' Oracle
            Return ":" & name
        End If
    End Function
#End Region

#Region "データベースサービスのタイプはOracleかどうかを判断する"
    ''' <summary>
    ''' データベースサービスのタイプはOracleかどうかを判断する
    ''' </summary>
    ''' <param name="db">Database</param>
    ''' <returns>Oracleの場合 true、そうではない場合は false</returns>
    ''' <remarks></remarks>
    Public Shared Function IsOracleDB(ByRef db As Database) As Boolean
        If TypeOf db.DbProviderFactory Is System.Data.SqlClient.SqlClientFactory Then
            ' 非Oracle
            Return False
        Else
            ' Oracle
            Return True
        End If
    End Function
#End Region

End Class
